package indi.bamboo.generate.mapper;

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @ClassName OracleTableMapper
 * @Description Oracle数据库表映射器接口
 * @Author Bamboo aspbamboo@gmail.com
 * @date 2018/11/8 0008下午 3:23
 * @Version 1.0
 */
public interface OracleTableMapper extends TableMapper{

    /**
     * 查询所有表名及其说明
     * @author Bamboo aspbamboo@gmail.com
     * @date 2018/11/08 15:26:31
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>> 所有表名及其说明
     */
    @Override
    @Select("SELECT" +
            "  t.TABLE_NAME AS tableName, " +
            "  f.COMMENTS " +
            "FROM" +
            "  USER_TABLES t " +
            "INNER JOIN USER_TAB_COMMENTS f " +
            "        ON t.TABLE_NAME = f.TABLE_NAME " +
            "ORDER BY " +
            "  t.TABLE_NAME")
    List<Map<String, Object>> selectAllTable(String databaseName);

    /**
     * 查询当前表所有字段
     * @author Bamboo aspbamboo@gmail.com
     * @date 2018/11/08 17:27:46
     * @param tableName 表名
     * @return java.util.List
     */
    @Override
    @Select("SELECT DISTINCT " +
            "  A.COLUMN_NAME NAME, " +
            "  A.DATA_TYPE DATA_TYPE, " +
            "  TO_CHAR(A.DATA_LENGTH, '999') DATA_LENGTH, " +
            "  A.NULLABLE ISNULL, " +
            "  CASE WHEN C.COLUMN_NAME = A.COLUMN_NAME " +
            "  THEN '是' " +
            "  ELSE '' " +
            "  END AS KEY,  " +
            "  B.COMMENTS COMMENTS, " +
            "  CASE WHEN A.DATA_TYPE = 'TIMESTAMP(6)' " +
            "  THEN '2' " +
            "  ELSE '1' " +
            "  END AS SORT " +
            "FROM " +
            "  USER_TAB_COLUMNS A " +
            "LEFT JOIN USER_COL_COMMENTS B " +
            "       ON A.COLUMN_NAME = B.COLUMN_NAME  " +
            "      AND A.TABLE_NAME = B.TABLE_NAME  " +
            "LEFT JOIN USER_CONS_COLUMNS C " +
            "       ON A.TABLE_NAME = C.TABLE_NAME " +
            "INNER JOIN	USER_CONSTRAINTS D " +
            "        ON A.TABLE_NAME = c.TABLE_NAME " +
            "       AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME " +
            "       AND D.CONSTRAINT_TYPE = 'P' " +
            "WHERE " +
            "  A.TABLE_NAME = #{tableName} " +
            "ORDER BY " +
            "  KEY, SORT, NAME")
    List selectFields(String tableName);
}